package com.zhengqiangblog.blog.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;

import com.zhengqiangblog.blog.dao.common.JdbcDaoSupport;
import com.zhengqiangblog.blog.model.Archive;
import com.zhengqiangblog.blog.model.Article;
import com.zhengqiangblog.core.RowMapper;

public class ArticleDao extends JdbcDaoSupport {

	private static final Logger logger = Logger.getLogger(ArticleDao.class);

	private ArticleRowMapper articleRowMapper = new ArticleRowMapper();

	public ArticleDao() {
		logger.info("ArticleDao initializing...");
	}

	public int saveArticle(Article article) throws Exception {
		String sql = "insert into article (category_id, title, author, post_date, edit_date, "
				+ "views, overview, content, status) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		Object[] args = new Object[9];
		args[0] = article.getCategoryId();
		args[1] = article.getTitle();
		args[2] = article.getAuthor();
		args[3] = article.getPostDate();
		args[4] = article.getEditDate();
		args[5] = article.getViews();
		args[6] = article.getOverview();
		args[7] = article.getContent();
		args[8] = article.getStatus();
		List<Object> keys = this.getJdbcTemplate().saveAndFetchGeneratedKeys(sql, args);
		return ((Long) keys.get(0)).intValue();
	}

	public Article getArticle(int articleId) throws Exception {
		String sql = "select * from article where id = ?";
		return this.getJdbcTemplate().queryForObject(sql, articleRowMapper, articleId);
	}

	public List<Integer> getArticleIdList() throws Exception {
		String sql = "select id from article where status = 0 order by id desc";
		return this.getJdbcTemplate().query(sql, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt("id");
			}
		});
	}

	public List<Integer> getArticleIdListByCategory(int categoryId) throws Exception {
		String sql = "select id from article where status = 0 and category_id = ? order by id desc";
		return this.getJdbcTemplate().query(sql, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt("id");
			}
		}, categoryId);
	}

	public int updateArticleStatus(int articleId, int status) throws Exception {
		String sql = "update article set status = ? where id = ?";
		return this.getJdbcTemplate().update(sql, status, articleId);
	}

	public int updateArticle(Article article) throws Exception {
		String sql = "update article set category_id = ?, title = ?, author = ?, "
				+ "edit_date = ?, overview = ?, content = ? where id = ?";
		Object[] args = new Object[7];
		args[0] = article.getCategoryId();
		args[1] = article.getTitle();
		args[2] = article.getAuthor();
		args[3] = article.getEditDate();
		args[4] = article.getOverview();
		args[5] = article.getContent();
		args[6] = article.getId();
		return this.getJdbcTemplate().update(sql, args);
	}

	public int deleteArticle(int articleId) throws Exception {
		String sql = "delete from article where id = ?";
		return this.getJdbcTemplate().update(sql, articleId);
	}

	public int getArticleCountByCategory(int categoryId) throws Exception {
		String sql = "select count(*) as count from article where status = 0 and category_id = ?";
		return this.getJdbcTemplate().queryForObject(sql, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt("count");
			}
		}, categoryId);
	}

	public List<Archive> getArchives() throws Exception {
		String sql = "select year(post_date) as post_date_year, month(post_date) as post_date_month, "
				+ "count(id) as count from article group by post_date_year, post_date_month "
				+ "order by post_date_year desc, post_date_month desc";

		return this.getJdbcTemplate().query(sql, new RowMapper<Archive>() {
			@Override
			public Archive mapRow(ResultSet rs, int rowNum) throws SQLException {
				Archive archive = new Archive();
				archive.setYear(rs.getInt("post_date_year"));
				archive.setMonth(rs.getInt("post_date_month"));
				archive.setCount(rs.getInt("count"));
				return archive;
			}
		});
	}

	public List<Integer> getArticleIdListByDate(int year, int month) throws Exception {
		String sql = "select id from article where status = 0 and year(post_date) = ? and month(post_date) = ? order by id desc";
		return this.getJdbcTemplate().query(sql, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt("id");
			}
		}, year, month);
	}

	public int updateArticleViews(int id, long views) throws Exception {
		String sql = "update article set views = ? where id = ?";
		return this.getJdbcTemplate().update(sql, views, id);
	}

	private class ArticleRowMapper implements RowMapper<Article> {
		@Override
		public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
			Article article = new Article();
			article.setId(rs.getInt("id"));
			article.setCategoryId(rs.getInt("category_id"));
			article.setTitle(rs.getString("title"));
			article.setAuthor(rs.getString("author"));
			article.setPostDate(rs.getTimestamp("post_date"));
			article.setEditDate(rs.getTimestamp("edit_date"));
			article.setViews(rs.getLong("views"));
			article.setOverview(rs.getString("overview"));
			article.setContent(rs.getString("content"));
			article.setStatus(rs.getInt("status"));
			return article;
		}
	}

}
